﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;

namespace HRM.DAL.Gateway
{
    public class ApplicationGateway : DBGateway
    {
        public bool SendApplication(int empId, LeaveApplication application)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText =
                    string.Format(
                        "insert into tblLeaveApplication values(@empId,@applyDate,@start,@end,@total,@type,@subject,@reason,@message,@status,@flag)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@empId", SqlDbType.Int).Value = empId;
                CommandObj.Parameters.Add("@applyDate", SqlDbType.DateTime).Value = application.ApplyDate;
                CommandObj.Parameters.Add("@start", SqlDbType.DateTime).Value = application.StartDate;
                CommandObj.Parameters.Add("@end", SqlDbType.DateTime).Value = application.EndDate;
                CommandObj.Parameters.Add("@total", SqlDbType.Int).Value = application.TotalDays;
                CommandObj.Parameters.Add("@type", SqlDbType.VarChar).Value = application.LeaveType;
                CommandObj.Parameters.Add("@subject", SqlDbType.VarChar).Value = application.Subject;
                CommandObj.Parameters.Add("@reason", SqlDbType.VarChar).Value = application.Reason;
                CommandObj.Parameters.Add("@message", SqlDbType.VarChar).Value = application.Message;
                CommandObj.Parameters.Add("@status", SqlDbType.VarChar).Value = "Pending";
                CommandObj.Parameters.Add("@flag", SqlDbType.VarChar).Value = "Unread";
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Message Not Send, Server Error !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<LeaveApplication> GetAllApplication()
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText =
                    @"SELECT emp.Name, emp.Status, tblCode.CodeName, desig.Title, tblProjects.ProjectName,app.Id,app.ApplyDate, app.StartDate, app.EndDate, app.TotalDays, 
                                        app.LeaveType,app.Subject, app.Reason, app.Message, app.Status,app.flag 
                                        FROM tblEmployees as emp Full outer JOIN tblCode ON emp.Code_CodeID = tblCode.Id Full outer JOIN tblDesignations as desig ON emp.DesignationID = desig.Id Full outer JOIN
                                        tblProjects ON emp.ProjectID = tblProjects.ProjectID Inner JOIN tblLeaveApplication as app ON emp.EmployeeId = app.EmpId order by app.ApplyDate DESC";
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<LeaveApplication> applications = new List<LeaveApplication>();
                while (reader.Read())
                {
                    Employee employee = new Employee();
                    Code code = new Code();
                    Designation designation = new Designation();
                    Project project = new Project();
                    LeaveApplication application = new LeaveApplication();
                    employee.Name = reader[0].ToString();
                    employee.Status = reader[1].ToString();
                    code.CodeName = reader[2].ToString();
                    designation.Title = reader[3].ToString();
                    project.ProjectName = reader[4].ToString();
                    employee.EmpCode = code;
                    employee.Designation = designation;
                    employee.Project = project;
                    application.Employee = employee;
                    application.Id = Convert.ToInt32(reader[5]);

                    DateTime apply = Convert.ToDateTime(reader[6]);
                    application.ApplyDate = apply.ToShortDateString();
                    DateTime strat = Convert.ToDateTime(reader[7]);
                    application.StartDate = strat.ToShortDateString();
                    DateTime end = Convert.ToDateTime(reader[8]);
                    application.EndDate = end.ToShortDateString();

                    application.TotalDays = Convert.ToInt32(reader[9]);
                    application.LeaveType = reader[10].ToString();
                    application.Subject = reader[11].ToString();
                    application.Reason = reader[12].ToString();
                    application.Message = reader[13].ToString();
                    application.Status = reader[14].ToString();
                    application.Flag = reader[15].ToString();
                    applications.Add(application);
                }
                return applications;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Retrive Application");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public LeaveApplication ReadApplication(int applicationId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText =
                    @"SELECT emp.Name, emp.Status, tblCode.CodeName, desig.Title, tblProjects.ProjectName,app.Id,app.ApplyDate, app.StartDate, app.EndDate, app.TotalDays, 
                                        app.LeaveType,app.Subject, app.Reason, app.Message, app.Status,app.flag 
                                        FROM tblEmployees as emp Full outer JOIN tblCode ON emp.Code_CodeID = tblCode.Id Full outer JOIN tblDesignations as desig ON emp.DesignationID = desig.Id Full outer JOIN
                                        tblProjects ON emp.ProjectID = tblProjects.ProjectID Inner JOIN tblLeaveApplication as app ON emp.EmployeeId = app.EmpId  where app.Id='" +
                    applicationId + "'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                LeaveApplication application = new LeaveApplication();
                while (reader.Read())
                {
                    Employee employee = new Employee();
                    Code code = new Code();
                    Designation designation = new Designation();
                    Project project = new Project();
                    employee.Name = reader[0].ToString();
                    employee.Status = reader[1].ToString();
                    code.CodeName = reader[2].ToString();
                    designation.Title = reader[3].ToString();
                    project.ProjectName = reader[4].ToString();
                    employee.EmpCode = code;
                    employee.Designation = designation;
                    employee.Project = project;
                    application.Employee = employee;
                    application.Id = Convert.ToInt32(reader[5]);
                    
                    DateTime apply = Convert.ToDateTime(reader[6]);
                    application.ApplyDate = apply.ToShortDateString();
                    DateTime strat = Convert.ToDateTime(reader[7]);
                    application.StartDate = strat.ToShortDateString();
                    DateTime end = Convert.ToDateTime(reader[8]);
                    application.EndDate = end.ToShortDateString();
                   
                    application.TotalDays = Convert.ToInt32(reader[9]);
                    application.LeaveType = reader[10].ToString();
                    application.Subject = reader[11].ToString();
                    application.Reason = reader[12].ToString();
                    application.Message = reader[13].ToString();
                    application.Status = reader[14].ToString();
                    application.Flag = reader[15].ToString();
                }
                return application;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Retrive Application");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public void UpdateFlag(int applicationId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText =
                    string.Format("Update tblLeaveApplication set flag=@flag where tblLeaveApplication.Id='" +
                                  applicationId + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@flag", SqlDbType.VarChar).Value = "Read";
                CommandObj.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw new Exception("Server Error !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public void RejectApplication(int applicationId,string status)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText =
                    string.Format("Update tblLeaveApplication set Status=@status where tblLeaveApplication.Id='" +
                                  applicationId + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
                CommandObj.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw new Exception("Server Error !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public int CountUnReadApplication()
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select count(flag) as unread from tblLeaveApplication where flag='Unread'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                int count = 0;
                while (reader.Read())
                {
                    count = Convert.ToInt32(reader[0]);
                }
                return count;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public void ApproveLeave(LeaveApplication application)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText =
                    string.Format("Update tblLeaveApplication set Status=@Status where tblLeaveApplication.Id='" +
                                  application.Id + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@Status", SqlDbType.VarChar).Value = "Approved";
                CommandObj.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw new Exception("Server Error !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool DeleteApplication(int id)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"delete from tblLeaveApplication where Id='" + id + "'";
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Deleting Application");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<LeaveApplication> GetApplicationStatus(int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"select * from tblLeaveApplication where EmpId='" + empId + "' order by ApplyDate DESC");
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<LeaveApplication> leaveApplications=new List<LeaveApplication>();
                while (reader.Read())
                {
                    LeaveApplication application=new LeaveApplication();
                    application.Id = Convert.ToInt32(reader[0]);
                    
                    DateTime apply = Convert.ToDateTime(reader[2]);
                    application.ApplyDate = apply.ToShortDateString();
                    DateTime strat = Convert.ToDateTime(reader[3]);
                    application.StartDate = strat.ToShortDateString();
                    DateTime end = Convert.ToDateTime(reader[4]);
                    application.EndDate = end.ToShortDateString();
                    
                    application.TotalDays = Convert.ToInt32(reader[5]);
                    application.LeaveType = reader[6].ToString();
                    application.Subject = reader[7].ToString();
                    application.Message = reader[9].ToString().Replace("</br>","13");
                    application.Status = reader[10].ToString();
                    application.Flag = reader[11].ToString();
                    leaveApplications.Add(application);
                }
                return leaveApplications;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}